package com.authine.cloudpivot.ext.applicationservice;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.map.MapBuilder;
import com.alibaba.cola.dto.SingleResponse;
import com.authine.cloudpivot.ext.Enum.*;
import com.authine.cloudpivot.ext.constants.AppConst;
import com.authine.cloudpivot.ext.constants.ModelConst;
import com.authine.cloudpivot.ext.dto.model.invoice.InvoiceApply;
import com.authine.cloudpivot.ext.dto.model.invoice.InvoiceExtendsAttr;
import com.authine.cloudpivot.ext.dto.model.report.HistoricalCostReport;
import com.authine.cloudpivot.ext.dto.model.settle.Settle;
import com.authine.cloudpivot.ext.dto.model.report.AccountReceivableReport;
import com.authine.cloudpivot.ext.dto.query.FinanceReportQuery;
import com.authine.cloudpivot.ext.dto.system.OrganizationDto;
import com.authine.cloudpivot.ext.dto.system.UserDto;
import com.authine.cloudpivot.ext.service.export.FinanceReportService;
import com.authine.cloudpivot.ext.service.export.impl.FinanceReportServiceImpl;
import com.authine.cloudpivot.ext.utils.*;
import com.authine.cloudpivot.ext.utils.system.OrgUtils;
import com.authine.cloudpivot.ext.utils.system.ReportUtils;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import com.authine.mvp.app.launcher.dto.bo.response.BO;
import io.swagger.v3.oas.annotations.Operation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.assertj.core.util.Maps;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.util.CollectionUtils;

import javax.ws.rs.POST;
import javax.ws.rs.Path;
import java.math.BigDecimal;
import java.util.*;
import java.util.function.Predicate;
import java.util.stream.Collectors;

import static com.authine.hermes.app.launcher.service.ApplicationServiceFoundation.CUSTOM_SERVICE;

/**
 * 应收报表，应付报表，成本报表导出
 */
@Path("FinanceReportController")
@Slf4j
public class FinanceReportController {
    NamedParameterJdbcTemplate jdbcTemplate =  ApplicationContextUtils.getBean(NamedParameterJdbcTemplate.class);
    private static  final FinanceReportService financeReportService = new FinanceReportServiceImpl();

    @Path("accountReceivableReport")
    @POST
    @Operation(summary = "平台交易管理-销售交易-应收报表",tags = CUSTOM_SERVICE)
    public SingleResponse accountReceivableReport(FinanceReportQuery query){
        //根据驻场人员负责的客户进行检索
        if (RegexUtils.notNull(query.getResidentName())) {
            List<UserDto> memberList = JdbcTemplateUtils.builder(AppConst.SYSTEM_MANAGEMENT, ModelConst.T_USER).selectField(" * ")
                    .like("fullname", query.getCustomerName())
                    .queryForPOJOList(UserDto.class, true);

            if (!CollectionUtils.isEmpty(memberList)) {
                String sql = "select  JSON_UNQUOTE(organization_id -> '$.id') from "+AppConst.OPERATION1_CENTER+".i_operation1center_t_platform_grant_user where " +
                        " JSON_UNQUOTE(member_id -> '$.id') in (:memberId) ";
                List<String> memberIds =  memberList.stream().map(UserDto::getId).collect(Collectors.toList());
                Map<String, Object> param = Maps.newHashMap("memberId", memberIds);
                Collection<String> grantedOrganizationIdList =  jdbcTemplate.queryForList(sql, param,String.class);

                query.setOrganizationIds(grantedOrganizationIdList.stream().filter(Objects::nonNull).collect(Collectors.toList()));
            } else {
                query.setOrganizationIds(Collections.singletonList("-1"));
            }
        }

        //获取业务部门跟负责人
        List<Map<String, Object>> orgList = new ArrayList<>();
        String noBusinessMemberSql = "select id from "+JdbcTemplateUtils.getTableName(ModelConst.T_ORGANIZATION,AppConst.SYSTEM_MANAGEMENT)+
                " where business_member_id is not null ";
        List<String> data = jdbcTemplate.queryForList(noBusinessMemberSql, new HashMap<>(),String.class);

        if (null != data) {
            for (String orgId : data) {
                String userSql = "select * from "+JdbcTemplateUtils.getTableName(ModelConst.T_USER, AppConst.SYSTEM_MANAGEMENT)+
                        " where  JSON_UNQUOTE(organization_id -> '$.id')  =  '"+orgId+"'";
                List<UserDto> data1 = jdbcTemplate.query(userSql, BeanPropertyRowMapper.newInstance(UserDto.class));
                for (UserDto user : data1) {
                    Map<String, Object> org = MapBuilder.create(new HashMap<String, Object>())
                            .put("organizationId", orgId)
                            .put("businessMemberName", user.getFullname())
                            .put("businessDepartmentName", user.getDepartment_name())
                            .build();
                    orgList.add(org);
                }
            }
        }
        List<AccountReceivableReport> reportDataList = this.accountReceivable(query);
        if (reportDataList.size() > 0) {
            Set<String> organizationIds = reportDataList.stream().map(t -> t.getCustomerOrganizationId()).collect(Collectors.toSet());

            List<OrganizationDto> orgActualPeriodList = OrgUtils.getOrgListByOrgIds(new ArrayList<>(organizationIds));

            for (AccountReceivableReport accountReceivableReport : reportDataList) {
                //获取真实账期
                List<OrganizationDto> mappingAccount = orgActualPeriodList.stream().filter(t -> t.getId().equals(accountReceivableReport.getCustomerOrganizationId())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(mappingAccount)) {
                    accountReceivableReport.setActualAccountPeriod(mappingAccount.get(0).getStatement_cycle());
                }

                if (!CollectionUtils.isEmpty(orgList)) {
                    List<Map<String, Object>> voList = orgList.stream()
                            .filter(o -> o.get("organizationId").toString().equals(accountReceivableReport.getCustomerOrganizationId())).collect(Collectors.toList());
                    if (!CollectionUtils.isEmpty(voList)) {
                        accountReceivableReport.setPrincipal(voList.get(0).get("businessMemberName").toString());
                        accountReceivableReport.setProjectDepartment(voList.get(0).get("businessDepartmentName").toString());
                    }
                }
            }
        }

        Predicate<AccountReceivableReport> predicates = (t) -> true;

        //根据父组织过滤
        if (RegexUtils.notNull(query.getCustomerParentName())) {
            OrganizationDto org = OrgUtils.getOrgByOrgName(query.getCustomerParentName());
            if (null != org) {
                List<OrganizationDto> orgListByOrgId = OrgUtils.getOrgListByOrgId(org.getId());
                predicates = predicates.and((t) -> orgListByOrgId.stream().map(OrganizationDto::getId).collect(Collectors.toList()).contains(t.getCustomerOrganizationId()));
            }
        }

        //根据项目负责人过滤
        if (RegexUtils.notNull(query.getPrincipal())) {
            predicates = predicates.and((t) -> query.getPrincipal().equals(t.getPrincipal()));
        }

        List<Map<String, Object>> maps = reportDataList.stream().filter(predicates).map(BeanUtil::beanToMap).collect(Collectors.toList());
        SingleResponse<BO> response = ReportUtils.exportReport("account_receivable_report", query.getReportType().getId(), maps);
        if (response.isSuccess()){
            return SingleResponse.buildSuccess();
        }else{
            return SingleResponse.buildFailure(response.getErrCode(), response.getErrMessage());
        }

    }

    private List<AccountReceivableReport> accountReceivable(FinanceReportQuery query){
        if(query.getStartDate() == null){
            query.setStartDate(DateUtil.parse("2018-01-01 00:00:00", DatePattern.NORM_DATETIME_PATTERN));
        }
        if (query.getEndDate() != null){
            query.setEndDate(DateUtil.endOfDay(query.getEndDate()));
        }
        List<InvoiceApply> invoiceList = JdbcTemplateUtils.builder(ModelConst.T_INVOICE_APPLY).selectField("*")
                .in("business_type", Arrays.asList(InvoiceBusinessType.Product.name(), InvoiceBusinessType.RedProduct.name()))
                .in("JSON_UNQUOTE(sale_organization_id -> '$.id')", query.getSaleOrganizationIds())
                .in("number", StringUtils.isNotBlank(query.getNumber()) ? Arrays.asList(StringUtils.split(query.getNumber(), ":")) : null)
                .in("JSON_UNQUOTE(purchase_organization_id -> '$.id')", query.getOrganizationIds())
                .like("purchase_name", query.getCustomerName())
                .gte("date", query.getStartDate())
                .lte("date", query.getEndDate())
                .in("statuss", Arrays.asList(InvoiceStatus.Done.name(), InvoiceStatus.ReceiverConfirming.name()))
                .queryForPOJOList(InvoiceApply.class);
        // 苏州涛杰光电科技有限公司（504） 和 王淼（401）金蝶已平账，并且以后没有业务往来，报表导出时自动过滤这两个抬头，广通暂时也不显示
        invoiceList = invoiceList.stream().filter(t -> !"苏州涛杰光电科技有限公司".equals(t.getPurchase_name())
                && !"王淼".equals(t.getPurchase_name())
                && !"上海广通工业品科技有限公司".equals(t.getPurchase_name())
        ).collect(Collectors.toList());

        if (CollectionUtils.isEmpty(invoiceList)) {
            return Collections.emptyList();
        }
        //从所有发票里过滤出红票
        List<InvoiceApply> redInvoiceList = invoiceList.stream().filter(t -> null != t.getOffset_id()).collect(Collectors.toList());

        //过滤已被全部红冲的红蓝票
        if (!CollectionUtils.isEmpty(redInvoiceList)) {
            //需要排除的全部红冲的发票ID
            List<String> excludeRedIds = new ArrayList<>();
            invoiceList = invoiceList.stream().filter(t -> {
                //获取蓝票对应的红票
                List<InvoiceApply> refInvoice = redInvoiceList.stream().filter(r -> r.getOffset_id().equals(t.getId())).collect(Collectors.toList());
                if (CollectionUtils.isEmpty(refInvoice)) {
                    return true;
                }

                BigDecimal redTotal = CalculatorUtils.sum(refInvoice, BiFunctionConst.invoiceApplyTotalFun);
                if (redTotal.abs().compareTo(t.getTotal()) != 0) {
                    return true;
                }

                excludeRedIds.addAll(refInvoice.stream().map(InvoiceApply::getId).collect(Collectors.toList()));
                return false;
            }).collect(Collectors.toList());

            if (!CollectionUtils.isEmpty(excludeRedIds)) {
                invoiceList = invoiceList.stream().filter(t -> !excludeRedIds.contains(t.getId())).collect(Collectors.toList());
            }
        }

        List<AccountReceivableReport> results = new ArrayList<>();
        //获取查询时间段内的实际收款金额
        String settleDetailSql = "select detail.invoice_apply_id,sum(detail.total) total, " +
                " group_concat(distinct settle.type) \"type\" ," +
                " group_concat(distinct settle.confirm_time order by settle.confirm_time desc separator ',') confirmTime," +
                " group_concat(distinct settle.purchase_memo) memo " +
                "  from "+JdbcTemplateUtils.getTableName(ModelConst.T_SETTLE_DETAIL)+" detail " +
                " left join "+ JdbcTemplateUtils.getTableName(ModelConst.T_SETTLE) +" settle on settle.id=detail.settle_id " +
                "where detail.deleted=0 and settle.deleted=0 and (settle.status<>'New' and settle.status<>'Returned' )" +
                " and detail.invoice_apply_id in (:invoiceIds) and settle.confirm_time>=:startDate and settle.confirm_time<=:endDate " +
                "group by detail.invoice_apply_id ";
        Map<String, Object> param = MapBuilder.create(new HashMap<String, Object>())
                .put("invoiceIds", invoiceList.stream().map(InvoiceApply::getId).collect(Collectors.toList()))
                .put("startDate", query.getStartDate())
                .put("endDate", query.getEndDate()).build();
        List<Map<String,Object>> settledForReportList = jdbcTemplate.queryForList(settleDetailSql, param);

        for (InvoiceApply invoice : invoiceList) {
            BigDecimal total = invoice.getTotal();
            String relatedId = InvoiceExtendsAttr.conversion(invoice.getExtends_attr()).orElse(InvoiceExtendsAttr.builder().build()).getOffsetIds();
            if (InvoiceBusinessType.valueOf(invoice.getBusiness_type()) == InvoiceBusinessType.RedProduct) {
                total = BigDecimalUtils.mul(total, "-1");
                relatedId = invoice.getOffset_id();
            }

            /*
              获取收款数据
             */
            BigDecimal actualReceipt = BigDecimal.ZERO;
            String paymentMethod = "";
            String paymentDate = "";
            String memo = "";
            //获取结算信息
            if (!CollectionUtils.isEmpty(settledForReportList)) {
                List<Map<String,Object>> settleDetailReports = settledForReportList.stream().filter(t -> t.get("invoice_apply_id").toString().equals(invoice.getId())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(settleDetailReports)) {
                    Map<String, Object> settleDetailReport = settleDetailReports.get(0);
                    String[] types = StringUtils.split(settleDetailReport.get("type").toString(), ",");
                    actualReceipt = new BigDecimal(settleDetailReport.get("total").toString());
                    paymentMethod = Arrays.stream(types).map(t -> SettleType.valueOf(t).des).collect(Collectors.joining(","));
                    paymentDate = settleDetailReport.get("confirmTime").toString();
                    memo = settleDetailReport.get("memo").toString();
                }
            }

            //真实账期
            int actualAccountPeriod = 0;

            //应收款余额
            BigDecimal accountReceivableBalance = BigDecimalUtils.sub(total, actualReceipt);
            //到期日
            Date expiryDate = DateUtil.offsetDay(invoice.getDate(), actualAccountPeriod);
            //逾期天数
            int daysOverdue = 0;
            if (accountReceivableBalance.compareTo(BigDecimal.ZERO) == 1) {
                daysOverdue = (int) (DateUtil.between(query.getEndDate(),expiryDate, DateUnit.DAY));
            }

            String whetherToExpire = "否";
            if (accountReceivableBalance.compareTo(BigDecimal.ZERO) == 1 &&DateUtil.betweenMs(expiryDate, query.getEndDate())<0) {
                whetherToExpire = "是";
            }

            //账龄计算
            double oneMonthAging = daysOverdue > 0 && daysOverdue <= 30 ? accountReceivableBalance.doubleValue() : 0.0;
            double twoMonthAging = daysOverdue > 30 && daysOverdue <= 60 ? accountReceivableBalance.doubleValue() : 0.0;
            double threeMonthAging = daysOverdue > 60 && daysOverdue <= 90 ? accountReceivableBalance.doubleValue() : 0.0;
            double moreMonthAging = daysOverdue > 90 ? accountReceivableBalance.doubleValue() : 0.0;

            //未到期金额
            BigDecimal unexpiredAmount = BigDecimalUtils.sub(accountReceivableBalance, oneMonthAging);
            unexpiredAmount = BigDecimalUtils.sub(unexpiredAmount, twoMonthAging);
            unexpiredAmount = BigDecimalUtils.sub(unexpiredAmount, threeMonthAging);
            unexpiredAmount = BigDecimalUtils.sub(unexpiredAmount, moreMonthAging);

            results.add(AccountReceivableReport.builder()
                    .customerOrganizationName(invoice.getPurchase_organization_name())
                    .customerOrganizationId(invoice.getPurchase_organization_id().getId())
                    .customerName(invoice.getPurchase_name())
                    .invoiceNumber(invoice.getNumber())
                    .date(DateUtil.format(invoice.getDate(), DatePattern.NORM_DATETIME_PATTERN))
                    .total(total.doubleValue())
                    .actualAccountPeriod(actualAccountPeriod)
                    .deadline(DateUtil.format(query.getEndDate(), DatePattern.NORM_DATETIME_PATTERN))
                    .paymentMethod(paymentMethod)
                    .actualReceipt(actualReceipt.doubleValue())
                    .expiryDate(DateUtil.format(expiryDate, DatePattern.NORM_DATETIME_PATTERN))
                    .paymentDate(paymentDate)
                    .accountReceivableBalance(accountReceivableBalance.doubleValue())
                    .daysOverdue(daysOverdue)
                    .oneMonthAging(oneMonthAging)
                    .twoMonthAging(twoMonthAging)
                    .threeMonthAging(threeMonthAging)
                    .moreMonthAging(moreMonthAging)
                    .unexpiredAmount(unexpiredAmount.doubleValue())
                    .memo(memo)
                    .whetherToExpire(whetherToExpire)
                    .relatedId(relatedId)
                    //设置平台组织名称
                    .platformOrgName(invoice.getSale_organization_name())
                    .build());
        }
        //如果按照发票号码查询则不需要查询预收款单
        if (!RegexUtils.notNull(query.getNumber())) {
            results.addAll(accountCollectedInAdvance(query));
        }
        return results;
    }

    private List<AccountReceivableReport> accountCollectedInAdvance(FinanceReportQuery query){
        List<Settle> advanceSettles = JdbcTemplateUtils.builder(ModelConst.T_SETTLE).selectField("*")
                .in("business_type", Arrays.asList(SettleBusinessType.SaleReceivable.name(), SettleBusinessType.SalePayment.name()))
                .notEq("statuss", SettleStatus.Returned.name())
                .gt("write_off_total", 0)
                .in("JSON_UNQUOTE(purchase_organization_id -> '$.id')", query.getOrganizationIds())
                .like("purchase_name", query.getCustomerName())
                .gte("confirm_time", query.getStartDate())
                .lte("confirm_time", query.getEndDate())
                .queryForPOJOList(Settle.class);

        if (CollectionUtils.isEmpty(advanceSettles)) {
            return Collections.emptyList();
        }

        List<AccountReceivableReport> results = new ArrayList<>();
        for (Settle advanceSettle : advanceSettles) {
            Double writeOffTotal = advanceSettle.getWrite_off_total().doubleValue();
            if (SettleBusinessType.valueOf(advanceSettle.getBusiness_type()) == SettleBusinessType.SalePayment) {
                writeOffTotal = -writeOffTotal;
            }

            //红冲发票会取不到PurchaseName
            String purchaseName = "".equals(advanceSettle.getPurchase_name()) ? advanceSettle.getPurchase_organization_name() : advanceSettle.getPurchase_name();

            results.add(AccountReceivableReport.builder()
                    .customerOrganizationName(advanceSettle.getPurchase_organization_name())
                    .customerOrganizationId(advanceSettle.getPurchase_organization_id().getId())
                    .customerName(purchaseName)
                    .deadline(DateUtil.format(query.getEndDate(), DatePattern.NORM_DATETIME_PATTERN))
                    .paymentMethod(SettleType.valueOf(advanceSettle.getType()).des)
                    .actualReceipt(writeOffTotal)
                    .paymentDate(DateUtil.format(advanceSettle.getConfirm_time(), DatePattern.NORM_DATETIME_PATTERN))
                    .accountReceivableBalance(-writeOffTotal)
                    .unexpiredAmount(-writeOffTotal)
                    .memo(advanceSettle.getPurchase_memo())
                    .whetherToExpire("否")
                    .relatedId(null == advanceSettle.getReference_id() ? "" : advanceSettle.getReference_id().toString())
                    .build());
        }

        return results;
    }

    @Path("exportCostReport")
    @POST
    @Operation(summary = "平台交易管理-销售交易-成本报表",tags = CUSTOM_SERVICE)
    public SingleResponse exportCostReport(FinanceReportQuery query){
        List<HistoricalCostReport> historicalCostReports = financeReportService.costReportData(query);
        List<Map<String, Object>> results = historicalCostReports.stream().map(BeanUtil::beanToMap).collect(Collectors.toList());
        SingleResponse<BO> response = ReportUtils.exportReport("cost_report", query.getReportType().getId(), results);
        if (response.isSuccess()){
            return SingleResponse.buildSuccess();
        }else{
            return SingleResponse.buildFailure(response.getErrCode(), response.getErrMessage());
        }
    }


    @Path("exportAccountPayableReport")
    @POST
    @Operation(summary = "平台交易管理-销售交易-应收报表",tags = CUSTOM_SERVICE)
    public SingleResponse exportAccountPayableReport(FinanceReportQuery query){
        List<Map<String, Object>> results = new ArrayList<>();

        List<Map<String, Object>> blueAccountPayableReports = this.accountPayableBlue(query);
        List<Map<String, Object>> redAccountPayableReports = this.accountPayableRed(query);

        if (!CollectionUtils.isEmpty(blueAccountPayableReports)) {
            results.addAll(blueAccountPayableReports);
        }
        if (!CollectionUtils.isEmpty(redAccountPayableReports)) {
            results.addAll(redAccountPayableReports);
        }

        if (!RegexUtils.notNull(query.getNumber())) {
            String exportDate = DateUtil.format(query.getEndDate(),DatePattern.NORM_DATETIME_PATTERN);

            List<Settle> settleList = JdbcTemplateUtils.builder(ModelConst.T_SETTLE).selectField(" * ")
                    .eq("business_type", SettleBusinessType.PurchaseReceivable.name())
                    .gt("write_off_total", 0)
                    .like("sale_organization_name", query.getCustomerName())
                    .lte("payment_time", DateUtil.format(query.getEndDate(), DatePattern.NORM_DATETIME_PATTERN))
                    .queryForPOJOList(Settle.class);
            if (!CollectionUtils.isEmpty(settleList)) {
                for (Settle advanceSettle : settleList) {
                    Map<String, Object> map = MapBuilder.create(new HashMap<String, Object>())
                            .put("vendorOrgName", advanceSettle.getSale_organization_name())
                            .put("platformOrgName", advanceSettle.getPurchase_organization_name())
                            .put("exportDate", exportDate)
                            .put("amountPaymentLast", advanceSettle.getTotal())
                            .put("accountPayableBalance", advanceSettle.getTotal()).build();

                    results.add(map);
                }
            }
        }
        SingleResponse<BO> response = ReportUtils.exportReport("account_payable_report", query.getReportType().getId(), results);
        if (response.isSuccess()){
            return SingleResponse.buildSuccess();
        }else{
            return SingleResponse.buildFailure(response.getErrCode(), response.getErrMessage());
        }
    }

    private List<Map<String, Object>> accountPayableBlue(FinanceReportQuery query){
        String currentStartDate = DateUtil.format(DateUtil.beginOfMonth(query.getEndDate()), DatePattern.NORM_DATETIME_PATTERN);
        String currentEndDate = DateUtil.format(DateUtil.endOfMonth(query.getEndDate()), DatePattern.NORM_DATETIME_PATTERN);
        String lastMonthsDeadline = DateUtil.format(DateUtil.endOfMonth(DateUtil.offsetMonth(query.getEndDate(),-1)), DatePattern.NORM_DATETIME_PATTERN);
        StringBuffer sql = new StringBuffer(" select invoice.id AS invoiceId, " +
                        "  invoice.number AS invoiceNumber, " +
                        "  invoice.sale_organization_name AS vendorOrgName, " +
                        "  invoice.purchase_organization_name AS platformOrgName, " +
                        "  invoice.confirm_time AS invoiceConfirmDate, " +
                        "  invoice.total AS invoiceAmount, " +
                        "  '"+ DateUtil.format(query.getEndDate(), "yyyy-MM")+ "' AS exportDate, " +
                        "  COALESCE(SUM(pdetail.total),0) AS amountPaymentLast, " +
                        "  COALESCE(SUM(pdetail_current.total),0)-COALESCE(SUM( pdetail_current.platform ),0)-COALESCE(SUM( pdetail_current.interest ),0) AS amountPayment, " +
                        "  COALESCE(SUM(pdetail_current.platform),0)+COALESCE(SUM( pdetail_current.interest ),0) AS subtotal, " +
                        "  COALESCE(SUM(pdetail_current.platform),0) AS platform, " +
                        "  COALESCE(SUM(pdetail_current.interest),0) AS interest, " +
                        "  case when invoice.total-COALESCE(SUM( pdetail.total ),0)-COALESCE(SUM( pdetail_current.total ),0)<0 then 0  " +
                        "  else invoice.total-COALESCE(SUM( pdetail.total ),0)-COALESCE(SUM( pdetail_current.total ),0) end AS accountPayableBalance");
        sql.append(" from " + JdbcTemplateUtils.getTableName(ModelConst.T_INVOICE_APPLY) + " invoice")
            .append(" left join ");
        String settleSql = "(select detail.invoice_apply_id AS invoice_apply_id, " +
                "                        sum(detail.total) AS total, " +
                "                        sum(detail.platform) AS platform, " +
                "                        sum(detail.interest) AS interest " +
                "                        from "+JdbcTemplateUtils.getTableName(ModelConst.T_SETTLE_DETAIL)+ " detail " +
                "                        left join "+ JdbcTemplateUtils.getTableName(ModelConst.T_SETTLE) +" settle on settle.id=detail.settle_id  " +
                "                        where settle.status='Done' and settle.deleted=0 and detail.deleted=0 and settle.business_type='PurchasePayment'  " +
                "                        and %s GROUP BY detail.invoice_apply_id)  " +
                "                        %s on %s.invoice_apply_id=invoice.id ";
        sql.append(String.format(settleSql, "settle.confirm_time <= '" + lastMonthsDeadline + "'", "pdetail", "pdetail"))
                .append("left join ")
                .append(String.format(settleSql, "settle.confirm_time>='" + currentStartDate + "' and settle.confirm_time <= '" + currentEndDate + "'", "pdetail_current", "pdetail_current"));
        sql.append(" where invoice.deleted = 0 and invoice.statuss = 'Done' and invoice.business_type = 'Product'  ")
                .append(" and invoice.confirm_time <= '" + currentEndDate +"'");
        //供应商名称
        if(RegexUtils.notNull(query.getCustomerName())){
            sql.append(" and invoice.sale_organization_name like '%"+ query.getCustomerName() +"%'  ");
        }
        //平台组织
        if (query.getPurchaseOrganizationIds() == null){
            sql.append(" and (JSON_UNQUOTE(invoice.purchase_organization_id ->'$.id') =1 or JSON_UNQUOTE(invoice.purchase_organization_id ->'$.id') =2)   ");
        }else{
            sql.append(" and JSON_UNQUOTE(apply.purchase_organization_id ->'$.id') = "+query.getPurchaseOrganizationIds());
        }
        //发票号码
        if(RegexUtils.notNull(query.getNumber())){
            sql.append(" and invoice.number like '%" + query.getNumber() + "%'  ");
        }
        sql.append(" group by invoice.id,invoice.sale_organization_name,invoice.number,invoice.confirm_time,invoice.total  ");
        sql.append(" order by invoice.confirm_time DESC ");
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql.toString(), new HashMap<>());
        return mapList;
    }

    private List<Map<String, Object>> accountPayableRed(FinanceReportQuery query){
        String currentEndDate = DateUtil.format(DateUtil.endOfMonth(query.getEndDate()), DatePattern.NORM_DATETIME_PATTERN);
        StringBuffer sql = new StringBuffer(" select invoice.id AS invoiceId, " +
                "  invoice.number AS invoiceNumber, " +
                "  invoice.sale_organization_name AS vendorOrgName, " +
                "  invoice.purchase_organization_name AS platformOrgName, " +
                "  invoice.confirm_time AS invoiceConfirmDate, " +
                "  invoice.total AS invoiceAmount, " +
                "  '"+ DateUtil.format(query.getEndDate(), "yyyy-MM")+ "' AS exportDate, " +
                "  0 AS amountPaymentLast, " +
                "  0 AS amountPayment, " +
                "  0 AS subtotal, " +
                "  0 AS platform, " +
                "  0 AS interest, " +
                "  -invoice.total AS accountPayableBalance");
        sql.append(" from " + JdbcTemplateUtils.getTableName(ModelConst.T_INVOICE_APPLY) + " invoice");
        sql.append(" where invoice.deleted = 0 and invoice.statuss = 'Done' and invoice.business_type = 'Product' ")
                .append(" and invoice.confirm_time <= '" + currentEndDate +"'  ");
        //供应商名称
        if(RegexUtils.notNull(query.getCustomerName())){
            sql.append(" and invoice.sale_organization_name like '%"+ query.getCustomerName() +"%'  ");
        }
        //平台组织
        if (query.getPurchaseOrganizationIds() == null){
            sql.append(" and (JSON_UNQUOTE(invoice.purchase_organization_id ->'$.id') =1 or JSON_UNQUOTE(invoice.purchase_organization_id ->'$.id') =2) \n ");
        }else{
            sql.append(" and JSON_UNQUOTE(invoice.purchase_organization_id ->'$.id') = "+query.getPurchaseOrganizationIds());
        }
        //发票号码
        if(RegexUtils.notNull(query.getNumber())){
            sql.append(" and invoice.number like '%" + query.getNumber() + "%'  ");
        }
        sql.append(" group by invoice.id,invoice.sale_organization_name,invoice.number,invoice.confirm_time,invoice.total  ");
        sql.append(" order by invoice.confirm_time DESC ");
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql.toString(), new HashMap<>());
        return mapList;
    }
}
